package com.ruoyi.weixin.shop.mapper;

import com.ruoyi.weixin.shop.common.mapper.CoreMapper;
import com.ruoyi.weixin.shop.domain.YxStoreOrder;
import com.ruoyi.weixin.shop.dto.ChartDataDto;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * @author hupeng
 * @date 2020-05-12
 */
@Repository
@Mapper
public interface StoreOrderMapper extends CoreMapper<YxStoreOrder> {

    @Select("SELECT COUNT(*) FROM yx_store_order WHERE pay_time >= TO_DATE('1970-1-1  8:0:0','YYYY-MM-DD HH24:MI:SS')+(${today}/(3600*24))")
    Integer countByPayTimeGreaterThanEqual(@Param("today") int today);

    @Select("SELECT COUNT(*) FROM yx_store_order WHERE pay_time < TO_DATE('1970-1-1  8:0:0','YYYY-MM-DD HH24:MI:SS')+(${today}/(3600*24))  " +
			" and pay_time >= TO_DATE('1970-1-1  8:0:0','YYYY-MM-DD HH24:MI:SS')+(${yesterday}/(3600*24))")
    Integer countByPayTimeLessThanAndPayTimeGreaterThanEqual(@Param("today") int today, @Param("yesterday") int yesterday);

    @Select("select nvl(sum(pay_price),0)  from yx_store_order " +
            " where refund_status=0 and is_del=0 and paid=1")
    Double sumTotalPrice();

    @Select("SELECT nvl(sum(pay_price),0) as num," +
            "to_char(create_time, 'MM-DD') as time " +
            " FROM yx_store_order where refund_status=0 and is_del=0 and paid=1 " +
			" and pay_time >= TO_DATE('1970-1-1  8:0:0','YYYY-MM-DD HH24:MI:SS')+(${time}/(3600*24))" +
            " GROUP BY to_char(create_time,'MM-DD') " +
            " ORDER BY 2 ASC")
    List<ChartDataDto> chartList(@Param("time") int time);

    @Select("SELECT count(id) as num," +
            "to_char(create_time, 'MM-DD') as time " +
            " FROM yx_store_order where refund_status=0 and is_del=0 and paid=1 " +
			" and pay_time >= TO_DATE('1970-1-1  8:0:0','YYYY-MM-DD HH24:MI:SS')+(${time}/(3600*24))" +
            " GROUP BY to_char(create_time,'MM-DD') " +
            " ORDER BY 2 ASC")
    List<ChartDataDto> chartListT(@Param("time") int time);
}
